from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import as_declarative, declared_attr
from sqlalchemy_utils import database_exists, create_database

from app.config import SQLALCHEMY_DATABASE_URI, MYSQL

from typing import Any

from sqlalchemy import Column, Integer, String, \
    Text, ForeignKey, DateTime, UniqueConstraint, \
        Index, Boolean, SmallInteger, BigInteger, Date
from sqlalchemy.orm import relationship
from datetime import datetime


engine = create_engine(
        SQLALCHEMY_DATABASE_URI,
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
session_local = sessionmaker(
    autocommit=False, autoflush=False, bind=engine)

# 模型基类
@as_declarative()
class Base:
    id: Any
    create_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    status = Column(Integer, default=1)  # 0为删除

    def delete(self):
        self.status = 0

###### 模型 ########
class Role(Base):
    __tablename__ = "role"
    id = Column(Integer, primary_key=True)
    name = Column(String(100), unique=True)
    auths = Column(String(600))

    users = relationship("User", backref='role')
    admins = relationship("Admin", backref='role')


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    email = Column(String(24), unique=True, nullable=False)
    nickname = Column(String(24), unique=True)
    auth = Column(SmallInteger, default=1)
    _password = Column('password', String(100))

    role_id = Column(Integer, ForeignKey('role.id')) # 所属角色

    userlogs = relationship('Userlog', backref='user')


class Admin(Base):
    __tablename__ = "admin"
    id = Column(Integer, primary_key=True)
    email = Column(String(24), unique=True, nullable=False)
    nickname = Column(String(24), unique=True)
    auth = Column(SmallInteger, default=9)
    _password = Column('password', String(100))

    role_id = Column(Integer, ForeignKey('role.id')) # 所属角色


class Movie(Base):
    __tablename__ = "movie"
    id = Column(Integer, primary_key=True)  # 记录id
    title = Column(String(255), unique=True)  # 电影标题
    url = Column(String(255), unique=True)  # 播放的url
    info = Column(Text)                       # 简介
    logo = Column(String(255), unique=True)  # 电影海报
    star = Column(SmallInteger)              # 电影星级
    playnum = Column(BigInteger)             # 播放数
    commentnum = Column(BigInteger)           # 评论数
    area = Column(String(255))        # 地区
    release_time = Column(Date)           # 发布时间
    length = Column(String(100))      # 电影长度
    ctime = Column(DateTime, index=True, default=datetime.now)

    tag_id = Column(Integer, ForeignKey('tag.id')) # 所属标签 外联tag表的id值
    
    comments = relationship("Comment", backref='movie')  # 评论表的外键
    moviecols = relationship("Moviecol", backref='movie')
    previews = relationship("Preview", backref='movie')


class Tag(Base):
    __tablename__ = "tag"
    id = Column(Integer, primary_key=True)  # 记录id
    name = Column(String(100), unique=True) # 标题

    movies = relationship("Movie", backref='tag')  # 关联查询 tag表


class Preview(Base):
    __tablename__ = "preview"
    id = Column(Integer, primary_key=True)  # 记录id
    title = Column(String(255), unique=True)  # 电影标题
    logo = Column(String(255), unique=True)  # 电影海报

    movie_id = Column(Integer, ForeignKey('movie.id'))   # move表的id


class Comment(Base):
    __tablename__ = 'comment'
    id = Column(Integer, primary_key=True)  # 记录id
    content = Column(Text)

    movie_id = Column(Integer,ForeignKey('movie.id'))   # move表的id
    user_id = Column(Integer, ForeignKey('user.id'))   # user表的id


class Moviecol(Base):
    __tablename__ = "moviecol"
    id = Column(Integer, primary_key=True)  # 记录id

    movie_id = Column(Integer, ForeignKey('movie.id'))   # move表的id
    user_id = Column(Integer, ForeignKey('user.id'))   # user表的id


class Userlog(Base):
    __tablename__ = "userlog"
    id = Column(Integer, primary_key=True)  # 记录的id
    ip = Column(String(100))  # 登录的IP

    user_id = Column(Integer, ForeignKey('user.id')) # 所属会员


class Adminlog(Base):
    __tablename__ = "adminlog"
    id = Column(Integer, primary_key=True)  # 记录的id
    ip = Column(String(100))  # 登录的IP

    admin_id = Column(Integer, ForeignKey('admin.id')) # 所属会员


class Oplog(Base):
    __tablename__ = "oplog"
    id = Column(Integer, primary_key=True)  # 记录的id
    ip = Column(String(100))  # 登录的IP
    reason =Column(String(600)) # 操作原因

    admin_id = Column(Integer, ForeignKey('admin.id')) # 所属会员


class Auth(Base):
    __tablename__ = "auth"
    id = Column(Integer, primary_key=True)  # 编号
    name = Column(String(100), unique=True)
    url = Column(String(255), unique=True)   # 可访问的url
###### 模型结束 ########




def create_db(engine):
    # 建库
    if not database_exists(engine.url):
        print('make db')
        create_database(engine.url)
    else:
        print("\ndb %s [OK]"% config.MYSQL["DB"])    

        
if __name__ == '__main__':
    # python 环境下 运行本文件自动建表建库
    create_db(engine)
    Base.metadata.create_all(engine)